package com.wyd;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.util.*;

@Slf4j
public class ExcelUtil {
    /**
     * 读取excel所有数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static Map<String, List<List<String>>> readExcel(String path) throws IOException, InvalidFormatException {
        return readExcel(WorkbookFactory.create(new File(path)));
    }

    /**
     * 读取excel所有数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static Map<String, List<List<String>>> readExcel(Workbook workbook) {
        if (workbook != null) {
            HashMap<String, List<List<String>>> items = new HashMap<>();

            FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator();

            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();

                items.put(sheet.getSheetName(), readSheet(sheet, formulaEvaluator));
            }

            try {
                workbook.close();
            } catch (IOException e) {
                log.error("关闭workbook错误", e);
            }

            return items;
        }

        return null;
    }

    /**
     * 读取excel默认sheet的所有数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static List<List<String>> readExcelDefaultSheet(String path) throws IOException, InvalidFormatException {
        return readExcelDefaultSheet(WorkbookFactory.create(new File(path)));
    }

    /**
     * 读取excel默认sheet的所有数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    public static List<List<String>> readExcelDefaultSheet(Workbook workbook) {
        if (workbook != null) {
            Sheet sheet = workbook.sheetIterator().next();

            List<List<String>> lists = readSheet(sheet, workbook.getCreationHelper().createFormulaEvaluator());

            try {
                workbook.close();
            } catch (IOException e) {
                log.error("关闭workbook错误", e);
            }

            return lists;
        }

        return null;
    }

    /**
     * 读取sheet数据
     * 时间格式：yyyy-MM-dd HH:mm:ss SSS
     */
    private static List<List<String>> readSheet(Sheet sheet, FormulaEvaluator formulaEvaluator) {
        List<List<String>> data = new ArrayList<>();

        DataFormatter dataFormatter = new DataFormatter();

        sheet.forEach(row -> {
            if (row != null) {
                ArrayList<String> strs = new ArrayList<>(row.getLastCellNum());
                // 此处循环row不能使用foreach row会忽略空列 导致数据错位
                for (int i = 0; i < row.getLastCellNum(); i++) {
                    Cell cell = row.getCell(i);
                    if (cell == null) {
                        strs.add(null);
                        continue;
                    }

                    // 时间格式单独处理
                    if (cell.getCellTypeEnum().equals(CellType.NUMERIC) && DateUtil.isCellDateFormatted(cell)) {
                        strs.add(DateFormatUtils.format(cell.getDateCellValue(), "yyyy-MM-dd HH:mm:ss SSS"));
                    } else {
                        strs.add(dataFormatter.formatCellValue(cell, formulaEvaluator));
                    }
                }

                data.add(strs);
            }
        });

        return data;
    }
}
